﻿using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using MySql.Data.MySqlClient;

namespace TestCalc
{
    public partial class GetExcel2 : Form
    {
        public GetExcel2()
        {
            InitializeComponent();
        }
        OleDbConnection conn = null;
        DataTable dt = null;
        OleDbDataAdapter dataAdapter = null;
        private void GetExcel2_Load(object sender, EventArgs e)
        {
            
            try
            {
                
                string str = @"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info = False;Data Source=E:\lzlj.xls;Extended Properties=Excel 8.0";
                //string str2 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\lzlj.xlsx;Extended Properties=Excel 12.0;HDR=YES;IMEX=1";
                conn = new OleDbConnection(str);
                //conn.Open();
                dataAdapter = new OleDbDataAdapter("select * from [sheet1$]", conn);
                dt = new DataTable();
                dataAdapter.Fill(dt);

                this.dataGridView1.DataSource = dt.DefaultView;
                //conn.Close();
                //int rows = dataGridView1.Rows.Count;
                //int cols = dataGridView1.Columns.Count;

                //MessageBox.Show("行数：" + rows + " 列数：" + cols);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("错误：" + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
           // outExcel(dataGridView1);
            DataGridviewShowToExcel(dataGridView1, true);
            //DataGridViewToExcel(dataGridView1);
        }


        //保存Excel方法1：将DataGridView中的数据载入到打开的Excel应用程序中，然后保存为xls文件。
        //
        public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
        {
            if (dgv.Rows.Count == 0)
                return false;
            //建立Excel对象   
            Excel.Application excel = new Excel.Application();
            Excel.Workbook wb = excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcle;
            //生成字段名称   
            for (int i = 0; i < dgv.ColumnCount; i++)
            {
                excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
            }
            //填充数据。
            for (int i = 0; i < dgv.RowCount - 1; i++)
            {
                for (int j = 0; j < dgv.ColumnCount; j++)
                {
                    if (dgv[j, i].ValueType == typeof(string))
                    {
                        excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
                    }
                    else
                    {
                        excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
                    }
                }
            }
            return true;
        }


        //#####################################################################################################################
        //得到的不是标准的Excel文件
        public static void DataGridViewToExcel(DataGridView dgv)
        {
            //申明保存对话框   
            SaveFileDialog dlg = new SaveFileDialog();
            //文件后缀列表   
            dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
            //默然路径是系统当前路径   
            dlg.InitialDirectory = Directory.GetCurrentDirectory();
            //打开保存对话框   
            if (dlg.ShowDialog() == DialogResult.Cancel) return;
            //返回文件路径   
            string fileNameString = dlg.FileName;
            //验证strFileName是否为空或值无效   
            if (fileNameString.Trim() == " ")
            { return; }
            //定义表格内数据的行数和列数   
            int rowscount = dgv.Rows.Count;
            int colscount = dgv.Columns.Count;
            //行数必须大于0   
            if (rowscount <= 0)
            {
                MessageBox.Show("没有数据可供保存", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //列数必须大于0   
            if (colscount <= 0)
            {
                MessageBox.Show("没有数据可供保存", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //行数不可以大于65536   
            if (rowscount > 65536)
            {
                MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //列数不可以大于255   
            if (colscount > 255)
            {
                MessageBox.Show("数据记录行数太多，不能保存", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //验证以fileNameString命名的文件是否存在，如果存在删除它   
            FileInfo file = new FileInfo(fileNameString);
            if (file.Exists)
            {
                try
                {
                    file.Delete();
                }
                catch (Exception error)
                {
                    MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }
            Excel.Application objExcel = null;
            Excel.Workbook objWorkbook = null;
            Excel.Worksheet objsheet = null;
            try
            {
                //申明对象   
                objExcel = new Microsoft.Office.Interop.Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
                //设置EXCEL不可见   
                objExcel.Visible = false;

                //向Excel中写入表格的表头   
                int displayColumnsCount = 1;
                for (int i = 0; i <= dgv.ColumnCount - 1; i++)
                {
                    if (dgv.Columns[i].Visible == true)
                    {
                        objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
                        displayColumnsCount++;
                    }
                }
                //设置进度条   
                //tempProgressBar.Refresh();   
                //tempProgressBar.Visible   =   true;   
                //tempProgressBar.Minimum=1;   
                //tempProgressBar.Maximum=dgv.RowCount;   
                //tempProgressBar.Step=1;   
                //向Excel中逐行逐列写入表格中的数据   
                for (int row = 0; row <= dgv.RowCount - 1; row++)
                {
                    //tempProgressBar.PerformStep();   

                    displayColumnsCount = 1;
                    for (int col = 0; col < colscount; col++)
                    {
                        if (dgv.Columns[col].Visible == true)
                        {
                            try
                            {
                                objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
                                displayColumnsCount++;
                            }
                            catch (Exception)
                            {

                            }

                        }
                    }
                }
                //隐藏进度条   
                //tempProgressBar.Visible   =   false;   
                //保存文件   
                objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            finally
            {
                //关闭Excel应用   
                if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
                if (objExcel != null) objExcel.Quit();

                objsheet = null;
                objWorkbook = null;
                objExcel = null;
            }
            MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }  













        //#######################################################################################################################
        //伪Excel文件，实际上是文本文件。
        public void outExcel(DataGridView gridView)
        {
            if (gridView.Rows.Count == 0)
            {
                MessageBox.Show("没有数据可供导出！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            else
            {

                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
                saveFileDialog.FilterIndex = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt = true;
                saveFileDialog.Title = "保存为Excel文件";
                saveFileDialog.ShowDialog();

                if (saveFileDialog.FileName.IndexOf(":") < 0) return; //被点了"取消"

                Stream myStream;
                myStream = saveFileDialog.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                string columnTitle = "";
                try
                {
                    //写入列标题
                    for (int i = 0; i < gridView.ColumnCount; i++)
                    {
                        columnTitle += gridView.Columns[i].HeaderText+"\t";
                    }
                    sw.WriteLine(columnTitle);

                    //写入列内容
                    for (int j = 0; j < gridView.Rows.Count; j++)
                    {
                        string columnValue = "";
                        for (int k = 0; k < gridView.Columns.Count; k++)
                        {
                            if (gridView.Rows[j].Cells[k].Value == null)
                                columnValue += "" + "\t";
                            else
                                columnValue += gridView.Rows[j].Cells[k].Value.ToString().Trim() + "\t";
                        }
                        sw.WriteLine(columnValue);
                    }
                    sw.Close();
                    myStream.Close();
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            //dt.Rows.Add(dt.NewRow());
            
            //this.dataGridView1.DataSource = dt.DefaultView;
            //MessageBox.Show(dt.Rows.Count.ToString());
            //dataGridView1.Rows.Add(1);
           // int colCount = dataGridView1.Columns.Count;
           // int rowCount = dataGridView1.Rows.Count;
            //dataGridView1[0, rowCount - 1].Value = tbID.Text.ToString();
           // dataGridView1[2, rowCount - 1].Value = tbValue.Text;
            // dataGridView1[4, rowCount - 1].Value = tbDesc.Text;
            //dt.Rows.Add(dt.NewRow());
            for (int i = 11; i < 20; i++) 
            {
                DataRow dr = dt.NewRow();
                dr["瓶号"] = i;
                dr["窖池号"] = 1;
                dr["酸度值"] = 6.7;
                dr["机器号"] = 2;
                dr["备注"] = "偏低";
                dt.Rows.Add(dr);
            }
            

        }

        private void btnShow_Click(object sender, EventArgs e)
        {
            int row = int.Parse(tbRow.Text.ToString());
            //int col = int.Parse(tbCol.Text.ToString());
            //lbShow.Text = dataGridView1[col, row].Value.ToString();
            string str = "";

            if (tbCol.Text.ToString() == "")
            {

                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                {
                    str += dataGridView1[i, row].Value.ToString() + " ; ";
                }
            }
            else
            {
                int col = int.Parse(tbCol.Text.ToString());
                str = dataGridView1[col, row].Value.ToString();
            }
            lbShow.Text = str;
        }

        private void btnSaveMySQL_Click(object sender, EventArgs e)
        {
            string strCon = "server = 192.168.1.101; user id = root; password = cally5075; database = test; persist security info = false";
            MySqlConnection conn = new MySqlConnection(strCon);
            MySqlCommand comm = null;        
            try
            {
                conn.Open();
                
                for (int j = 0; j < dataGridView1.Rows.Count - 1; j++)
                {
                    string sqlcmd = "insert into lzlj1 values(null,'";
                    for (int k = 1; k < dataGridView1.Columns.Count - 1; k++)
                    {
                        if (dataGridView1.Rows[j].Cells[k].Value != null)
                        {
                            if (dataGridView1.Rows[j].Cells[k].Value.ToString() == "")
                            {
                                sqlcmd = sqlcmd.Substring(0, sqlcmd.Length - 1);
                                sqlcmd += "null,'";
                            }
                            else
                                sqlcmd += dataGridView1.Rows[j].Cells[k].Value.ToString().Trim() + "','";
                        }
                    }
                    if (dataGridView1.Rows[j].Cells[dataGridView1.Columns.Count - 1].Value != null)
                    {
                        if (dataGridView1.Rows[j].Cells[dataGridView1.Columns.Count - 1].Value.ToString() == "")
                        {
                            sqlcmd = sqlcmd.Substring(0, sqlcmd.Length - 1);
                            sqlcmd += "null";
                        }
                        else
                            sqlcmd += dataGridView1.Rows[j].Cells[dataGridView1.Columns.Count - 1].Value.ToString().Trim() + "'";
                    }
                    sqlcmd += ");";
                    comm = new MySqlCommand(sqlcmd, conn);
                    comm.ExecuteNonQuery();
                    //MessageBox.Show(sqlcmd);
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("数据库读取错误：" + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
